from datetime import datetime, date, timedelta
from typing import List, Optional, Dict, Any
from sqlalchemy import select, update, delete, desc, and_, or_, func
from sqlalchemy.ext.asyncio import AsyncSession
from module_speech.entity.do.speech_client_user_do import SpeechClientUserDO
from module_speech.entity.vo.speech_client_vo import ClientUserQueryModel


class ClientUserDao:
    """
    客户端用户数据访问层
    """

    @classmethod
    async def get_client_user_by_api_key(cls, db: AsyncSession, api_key: str) -> Optional[SpeechClientUserDO]:
        """
        根据API密钥获取客户端用户

        :param db: 数据库会话
        :param api_key: API密钥
        :return: 客户端用户对象或None
        """
        query = select(SpeechClientUserDO).where(
            and_(
                SpeechClientUserDO.api_key == api_key,
                SpeechClientUserDO.del_flag == 0,
                SpeechClientUserDO.status == 1  # 启用状态
            )
        )
        result = await db.execute(query)
        return result.scalars().first()

    @classmethod
    async def get_client_user_by_id(cls, db: AsyncSession, client_user_id: int) -> Optional[SpeechClientUserDO]:
        """
        根据客户端用户ID获取用户详情

        :param db: 数据库会话
        :param client_user_id: 客户端用户ID
        :return: 客户端用户对象或None
        """
        query = select(SpeechClientUserDO).where(
            and_(SpeechClientUserDO.client_user_id == client_user_id, SpeechClientUserDO.del_flag == 0)
        )
        result = await db.execute(query)
        return result.scalars().first()

    @classmethod
    async def get_client_user_by_username(cls, db: AsyncSession, username: str) -> Optional[SpeechClientUserDO]:
        """
        根据用户名获取客户端用户

        :param db: 数据库会话
        :param username: 用户名
        :return: 客户端用户对象或None
        """
        query = select(SpeechClientUserDO).where(
            and_(SpeechClientUserDO.username == username, SpeechClientUserDO.del_flag == 0)
        )
        result = await db.execute(query)
        return result.scalars().first()

    @classmethod
    async def get_client_user_by_email(cls, db: AsyncSession, email: str) -> Optional[SpeechClientUserDO]:
        """
        根据邮箱获取客户端用户

        :param db: 数据库会话
        :param email: 邮箱地址
        :return: 客户端用户对象或None
        """
        query = select(SpeechClientUserDO).where(
            and_(SpeechClientUserDO.email == email, SpeechClientUserDO.del_flag == 0)
        )
        result = await db.execute(query)
        return result.scalars().first()

    @classmethod
    async def add_client_user_dao(cls, db: AsyncSession, client_user: SpeechClientUserDO) -> SpeechClientUserDO:
        """
        新增客户端用户

        :param db: 数据库会话
        :param client_user: 客户端用户对象
        :return: 新增的客户端用户对象
        """
        db.add(client_user)
        await db.flush()
        await db.refresh(client_user)
        return client_user

    @classmethod
    async def update_client_user_dao(cls, db: AsyncSession, client_user: SpeechClientUserDO) -> SpeechClientUserDO:
        """
        更新客户端用户信息

        :param db: 数据库会话
        :param client_user: 客户端用户对象
        :return: 更新后的客户端用户对象
        """
        client_user.update_time = datetime.now()
        await db.flush()
        await db.refresh(client_user)
        return client_user

    @classmethod
    async def get_client_users_list(cls, db: AsyncSession, query_object: ClientUserQueryModel) -> List[SpeechClientUserDO]:
        """
        获取客户端用户列表

        :param db: 数据库会话
        :param query_object: 查询参数对象
        :return: 客户端用户列表
        """
        query = select(SpeechClientUserDO).where(SpeechClientUserDO.del_flag == 0)
        
        # 添加查询条件
        if query_object.username:
            query = query.where(SpeechClientUserDO.username.like(f'%{query_object.username}%'))
        if query_object.email:
            query = query.where(SpeechClientUserDO.email == query_object.email)
        if query_object.client_type:
            query = query.where(SpeechClientUserDO.client_type == query_object.client_type)
        if query_object.plan_type:
            query = query.where(SpeechClientUserDO.plan_type == query_object.plan_type)
        if query_object.status is not None:
            query = query.where(SpeechClientUserDO.status == query_object.status)
        if query_object.is_verified is not None:
            query = query.where(SpeechClientUserDO.is_verified == query_object.is_verified)
        if query_object.organization:
            query = query.where(SpeechClientUserDO.organization.like(f'%{query_object.organization}%'))
        if query_object.create_time_start:
            query = query.where(SpeechClientUserDO.create_time >= query_object.create_time_start)
        if query_object.create_time_end:
            query = query.where(SpeechClientUserDO.create_time <= query_object.create_time_end)
        
        # 按创建时间倒序排列
        query = query.order_by(desc(SpeechClientUserDO.create_time))
        
        # 分页
        offset = (query_object.page_num - 1) * query_object.page_size
        query = query.offset(offset).limit(query_object.page_size)
        
        result = await db.execute(query)
        return result.scalars().all()

    @classmethod
    async def count_client_users(cls, db: AsyncSession, query_object: ClientUserQueryModel) -> int:
        """
        统计客户端用户总数

        :param db: 数据库会话
        :param query_object: 查询参数对象
        :return: 用户总数
        """
        query = select(func.count(SpeechClientUserDO.client_user_id)).where(SpeechClientUserDO.del_flag == 0)
        
        # 添加查询条件
        if query_object.username:
            query = query.where(SpeechClientUserDO.username.like(f'%{query_object.username}%'))
        if query_object.email:
            query = query.where(SpeechClientUserDO.email == query_object.email)
        if query_object.client_type:
            query = query.where(SpeechClientUserDO.client_type == query_object.client_type)
        if query_object.plan_type:
            query = query.where(SpeechClientUserDO.plan_type == query_object.plan_type)
        if query_object.status is not None:
            query = query.where(SpeechClientUserDO.status == query_object.status)
        if query_object.is_verified is not None:
            query = query.where(SpeechClientUserDO.is_verified == query_object.is_verified)
        if query_object.organization:
            query = query.where(SpeechClientUserDO.organization.like(f'%{query_object.organization}%'))
        if query_object.create_time_start:
            query = query.where(SpeechClientUserDO.create_time >= query_object.create_time_start)
        if query_object.create_time_end:
            query = query.where(SpeechClientUserDO.create_time <= query_object.create_time_end)
        
        result = await db.execute(query)
        return result.scalar()

    @classmethod
    async def update_quota_usage(cls, db: AsyncSession, client_user_id: int, usage_minutes: int) -> bool:
        """
        更新配额使用量

        :param db: 数据库会话
        :param client_user_id: 客户端用户ID
        :param usage_minutes: 使用的分钟数
        :return: 更新是否成功
        """
        query = update(SpeechClientUserDO).where(
            and_(SpeechClientUserDO.client_user_id == client_user_id, SpeechClientUserDO.del_flag == 0)
        ).values(
            quota_used_monthly=SpeechClientUserDO.quota_used_monthly + usage_minutes,
            update_time=datetime.now()
        )
        
        result = await db.execute(query)
        return result.rowcount > 0

    @classmethod
    async def reset_monthly_quota(cls, db: AsyncSession, client_user_id: int) -> bool:
        """
        重置月度配额

        :param db: 数据库会话
        :param client_user_id: 客户端用户ID
        :return: 重置是否成功
        """
        next_month = date.today().replace(day=1) + timedelta(days=32)
        next_month = next_month.replace(day=1)
        
        query = update(SpeechClientUserDO).where(
            and_(SpeechClientUserDO.client_user_id == client_user_id, SpeechClientUserDO.del_flag == 0)
        ).values(
            quota_used_monthly=0,
            quota_reset_date=next_month,
            update_time=datetime.now()
        )
        
        result = await db.execute(query)
        return result.rowcount > 0

    @classmethod
    async def verify_api_key(cls, db: AsyncSession, api_key: str, api_secret: str) -> Optional[SpeechClientUserDO]:
        """
        验证API密钥和密码

        :param db: 数据库会话
        :param api_key: API密钥
        :param api_secret: API密码
        :return: 验证成功的客户端用户对象或None
        """
        query = select(SpeechClientUserDO).where(
            and_(
                SpeechClientUserDO.api_key == api_key,
                SpeechClientUserDO.api_secret == api_secret,
                SpeechClientUserDO.del_flag == 0,
                SpeechClientUserDO.status == 1  # 启用状态
            )
        )
        result = await db.execute(query)
        return result.scalars().first()

    @classmethod
    async def update_last_login(cls, db: AsyncSession, client_user_id: int, login_ip: str) -> bool:
        """
        更新最后登录信息

        :param db: 数据库会话
        :param client_user_id: 客户端用户ID
        :param login_ip: 登录IP
        :return: 更新是否成功
        """
        query = update(SpeechClientUserDO).where(
            and_(SpeechClientUserDO.client_user_id == client_user_id, SpeechClientUserDO.del_flag == 0)
        ).values(
            last_login_time=datetime.now(),
            last_login_ip=login_ip,
            update_time=datetime.now()
        )
        
        result = await db.execute(query)
        return result.rowcount > 0

    @classmethod
    async def update_api_keys(cls, db: AsyncSession, client_user_id: int, api_key: str, api_secret: str) -> bool:
        """
        更新API密钥

        :param db: 数据库会话
        :param client_user_id: 客户端用户ID
        :param api_key: 新的API密钥
        :param api_secret: 新的API密码
        :return: 更新是否成功
        """
        query = update(SpeechClientUserDO).where(
            and_(SpeechClientUserDO.client_user_id == client_user_id, SpeechClientUserDO.del_flag == 0)
        ).values(
            api_key=api_key,
            api_secret=api_secret,
            update_time=datetime.now()
        )
        
        result = await db.execute(query)
        return result.rowcount > 0

    @classmethod
    async def check_quota_available(cls, db: AsyncSession, client_user_id: int, required_minutes: int) -> bool:
        """
        检查配额是否可用

        :param db: 数据库会话
        :param client_user_id: 客户端用户ID
        :param required_minutes: 需要的分钟数
        :return: 配额是否充足
        """
        query = select(SpeechClientUserDO.quota_monthly, SpeechClientUserDO.quota_used_monthly).where(
            and_(SpeechClientUserDO.client_user_id == client_user_id, SpeechClientUserDO.del_flag == 0)
        )
        result = await db.execute(query)
        quota_info = result.first()
        
        if not quota_info:
            return False
        
        quota_monthly, quota_used_monthly = quota_info
        remaining_quota = quota_monthly - quota_used_monthly
        return remaining_quota >= required_minutes

    @classmethod
    async def get_users_need_quota_reset(cls, db: AsyncSession) -> List[SpeechClientUserDO]:
        """
        获取需要重置配额的用户列表

        :param db: 数据库会话
        :return: 需要重置配额的用户列表
        """
        today = date.today()
        query = select(SpeechClientUserDO).where(
            and_(
                SpeechClientUserDO.del_flag == 0,
                SpeechClientUserDO.quota_reset_date <= today
            )
        )
        result = await db.execute(query)
        return result.scalars().all()

    @classmethod
    async def get_client_user_statistics(cls, db: AsyncSession) -> Dict[str, Any]:
        """
        获取客户端用户统计信息

        :param db: 数据库会话
        :return: 统计信息
        """
        # 总用户数
        total_query = select(func.count(SpeechClientUserDO.client_user_id)).where(SpeechClientUserDO.del_flag == 0)
        
        # 活跃用户数（状态为启用）
        active_query = select(func.count(SpeechClientUserDO.client_user_id)).where(
            and_(SpeechClientUserDO.del_flag == 0, SpeechClientUserDO.status == 1)
        )
        
        # 已验证用户数
        verified_query = select(func.count(SpeechClientUserDO.client_user_id)).where(
            and_(SpeechClientUserDO.del_flag == 0, SpeechClientUserDO.is_verified == 1)
        )
        
        # 按计划类型统计
        plan_type_query = select(
            SpeechClientUserDO.plan_type,
            func.count(SpeechClientUserDO.client_user_id).label('count')
        ).where(SpeechClientUserDO.del_flag == 0).group_by(SpeechClientUserDO.plan_type)
        
        # 按客户端类型统计
        client_type_query = select(
            SpeechClientUserDO.client_type,
            func.count(SpeechClientUserDO.client_user_id).label('count')
        ).where(SpeechClientUserDO.del_flag == 0).group_by(SpeechClientUserDO.client_type)
        
        # 执行查询
        total_result = await db.execute(total_query)
        active_result = await db.execute(active_query)
        verified_result = await db.execute(verified_query)
        plan_type_result = await db.execute(plan_type_query)
        client_type_result = await db.execute(client_type_query)
        
        # 处理结果
        total_users = total_result.scalar() or 0
        active_users = active_result.scalar() or 0
        verified_users = verified_result.scalar() or 0
        
        by_plan_type = {row.plan_type: row.count for row in plan_type_result}
        by_client_type = {str(row.client_type): row.count for row in client_type_result}
        
        return {
            'total_users': total_users,
            'active_users': active_users,
            'verified_users': verified_users,
            'by_plan_type': by_plan_type,
            'by_client_type': by_client_type
        }

    @classmethod
    async def delete_client_user_dao(cls, db: AsyncSession, client_user_id: int) -> bool:
        """
        删除客户端用户（软删除）

        :param db: 数据库会话
        :param client_user_id: 客户端用户ID
        :return: 删除是否成功
        """
        query = update(SpeechClientUserDO).where(
            and_(SpeechClientUserDO.client_user_id == client_user_id, SpeechClientUserDO.del_flag == 0)
        ).values(del_flag=1, update_time=datetime.now())
        
        result = await db.execute(query)
        return result.rowcount > 0

    @classmethod
    async def batch_update_quota_reset(cls, db: AsyncSession, user_ids: List[int]) -> bool:
        """
        批量重置配额

        :param db: 数据库会话
        :param user_ids: 用户ID列表
        :return: 更新是否成功
        """
        next_month = date.today().replace(day=1) + timedelta(days=32)
        next_month = next_month.replace(day=1)
        
        query = update(SpeechClientUserDO).where(
            and_(SpeechClientUserDO.client_user_id.in_(user_ids), SpeechClientUserDO.del_flag == 0)
        ).values(
            quota_used_monthly=0,
            quota_reset_date=next_month,
            update_time=datetime.now()
        )
        
        result = await db.execute(query)
        return result.rowcount > 0 